package com.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBUtils {
	
	private static final String DE_URL ="D:/WorkSoft/WorkSpace/Study/JavaSimpleExamples/src/com/common/config/jdbc.properties";
	private static PropertiesUtils PU ;
	public Connection conn;
	
	/** 构造方法 **/
	private DBUtils() {
		PU = PropertiesUtils.getInstance(DE_URL);
		conn = reConn();
	}

	private DBUtils(String URL) {
		PU = PropertiesUtils.getInstance(URL);
		conn = reConn();
	}
	
	private DBUtils(File profile) {
		PU = PropertiesUtils.getInstance(profile);
		conn = reConn();
	}
	
	private DBUtils(FileInputStream fileInputStream) {
		PU = PropertiesUtils.getInstance(fileInputStream);
		conn = reConn();
	}
	
	/** 获得DBUtils 实例 **/
	public static DBUtils getInstance(){
		return new DBUtils();
	}
	
	public static DBUtils getInstance(String URL){
		return new DBUtils(URL);
	}
	
	public static DBUtils getInstance(File profile){
		return new DBUtils(profile);
	}
	
	public static DBUtils getInstance(FileInputStream fileInputStream){
		return new DBUtils(fileInputStream);
	}
	
	private Connection reConn(){
		Connection conn = null;
		String db_type = PU.getValue("DB_TYPE");
		try {
			Class.forName(PropertiesUtils.getValue(db_type+"_DRIVER")).getInterfaces();
			conn = DriverManager.getConnection(PropertiesUtils.getValue(db_type+"_URL"),
					PropertiesUtils.getValue(db_type+"_USER"), PropertiesUtils.getValue(db_type+"_PSW"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public List<String[]> find(String sql,int column){
		List<String[]> list = new ArrayList<String[]>();
		if(conn == null) return list;
		try {
			conn.setAutoCommit(false);
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()){
				String[] strs = new String[column];
				for(int i=0;i<column;i++){
					strs[i]=rs.getString(i+1);
				}
				list.add(strs);
			}
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<String[]> find(String sql){
		List<String[]> list = new ArrayList<String[]>();
		if(conn == null) return list;
		try {
			conn.setAutoCommit(false);
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while(rs.next()){
				String[] strs = new String[columnCount];
				for(int i=0;i<columnCount;i++){
					strs[i]=rs.getString(i+1);
				}
				list.add(strs);
			}
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public int update(String sql){
		int count = 0;
		if(conn == null) return count;
		try {
			conn.setAutoCommit(false);
			Statement stat = conn.createStatement();
			count = stat.executeUpdate(sql);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
}
